package com.css.fxfzdzzh.modules.sandLiquefaction.repository.impl;

import com.alibaba.fastjson.JSONObject;
import com.css.fxfzdzzh.constants.YNEnum;
import com.css.fxfzdzzh.enums.DisasterTypeEnum;
import com.css.fxfzdzzh.enums.ResultEnum;
import com.css.fxfzdzzh.modules.eqLandslide.count.param.TaskModelParam;
import com.css.fxfzdzzh.modules.evaluate.entity.TaskVersion;
import com.css.fxfzdzzh.modules.sandLiquefaction.entity.*;
import com.css.fxfzdzzh.modules.sandLiquefaction.repository.SandLiquefactionRepository;
import com.css.fxfzdzzh.modules.sandLiquefaction.vo.*;
import com.css.fxfzdzzh.modules.taskManage.entity.TaskEntity;
import com.css.fxfzdzzh.modules.tifData.service.ElevationMapService;
import com.css.fxfzdzzh.util.PlatformDateUtils;
import com.css.fxfzdzzh.util.PlatformObjectUtils;
import com.css.fxfzdzzh.util.PlatformSessionUtils;
import com.css.fxfzdzzh.util.UUIDGenerator;
import com.css.fxfzdzzh.web.PlatformSessionContext;
import org.apache.commons.lang3.StringUtils;
import org.omg.CORBA.portable.Delegate;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.*;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import javax.inject.Named;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author Dbj
 * @date 2023年03月11日  11:11
 */
@Repository
public class SandLiquefactionRepositoryImpl implements SandLiquefactionRepository {

    @Resource
    JdbcTemplate jdbcTemplate;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Resource
    ElevationMapService elevationMapService;

    @Override
    public Map<String, Object> getPage(TaskModelParam param, int curPage, int pageSize) {
        String userId = PlatformSessionUtils.getUserId();
        String sql =
        "select task.dt_id,task.task_num,task.task_name,task.task_district,CASE"
            + " WHEN mo.algorithm_type='1' THEN '场地评估-标贯法'"
            + " WHEN mo.algorithm_type='2' THEN '场地评估-液化指数法'"
            + " WHEN mo.algorithm_type is null THEN ''"
            + " else '区域评估-' end as algorithm_type "
            + ",CASE"
            + " WHEN mo.operands='1' THEN '（饱和砂土或粉土）'"
            + " WHEN mo.operands='2' THEN '（饱和黄土）'"
            + " WHEN mo.operands is null THEN ''"
            + " else '层次分析法' end as operands from dzzh_task task left join dzzh_model_sand_liquefaction mo on task.dt_id=mo.task_id and (mo.result_status = '2' or  mo.result_status is null) and (mo.del_flag = '0' or  mo.del_flag is null) "
            + " where task.del_flag='0' and position('2' in disaster_type) > 0 and task.create_user='" + userId + "' and (task.task_status='1' or task.task_status='4' or task.task_status is null) ";
         String countSql =
        "SELECT count(*) from dzzh_task task left join dzzh_model_sand_liquefaction mo on task.dt_id=mo.task_id AND ( mo.del_flag = '0' OR mo.del_flag IS NULL ) and (mo.result_status = '2' or  mo.result_status is null) "
            + " where task.del_flag='0' and position('2' in disaster_type) > 0 and task.create_user='" + userId + "' and (task.task_status='1' or task.task_status='4' or task.task_status is null)";
        String orderSql = " order by task.create_time desc";
        if (PlatformObjectUtils.isNotEmpty(param.getTaskNum())) {
            sql = sql + " and task.task_num='" + param.getTaskNum()+"'";
            countSql = countSql + " and task.task_num='" + param.getTaskNum()+"'";
        }
        if (PlatformObjectUtils.isNotEmpty(param.getTaskName())) {
            sql = sql + " and task.task_name like '%"+param.getTaskName()+"%'";
            countSql = countSql + " and task.task_name like '%"+param.getTaskName()+"%'";
        }
        if (PlatformObjectUtils.isNotEmpty(param.getProvince())) {
            if (PlatformObjectUtils.isNotEmpty(param.getCity())) {
                String city = "-" + param.getCity();
                if(param.getProvince().equals(param.getCity())){
                    city = "";
                }
                if (PlatformObjectUtils.isNotEmpty(param.getArea())) {
                    sql = sql + " and (task.task_district like '%" + param.getProvince() + city + "-" + param.getArea() + "%'" +
                            " or task.task_district like '%" + param.getProvince() + city + "%' or task.task_district = '" + param.getProvince() + city +"')";
                            //"' or task.task_district like '%" + param.getProvince() + "%' or task.task_district = '" + param.getProvince() + "')";
                    countSql = countSql + " and (task.task_district like '%" + param.getProvince() + city + "-" + param.getArea() + "%'" +
                            " or task.task_district like '%" + param.getProvince() + city + "%' or task.task_district = '" + param.getProvince() + city +"')";
                            //"' or task.task_district like '%" + param.getProvince() + "%' or task.task_district = '" + param.getProvince() + "')";
                } else {
                    sql = sql + " and (task.task_district like '%" + param.getProvince() + city + "%' or task.task_district = '" + param.getProvince() + city +"')";
//                            "' or task.task_district like '%" + param.getProvince() + "%' or task.task_district = '" + param.getProvince() + "')";
                    countSql = countSql + " and (task.task_district like '%" + param.getProvince() + city + "%' or task.task_district = '" + param.getProvince() + city +"')";
                            //"' or task.task_district like '%" + param.getProvince() + "%' or task.task_district = '" + param.getProvince() + "')";
                }
            } else {
                sql = sql + " and (task.task_district like '%" + param.getProvince() + "%' or task.task_district = '" + param.getProvince() + "')";
                countSql = countSql + " and ( task.task_district like '%" + param.getProvince() + "%' or task.task_district = '" + param.getProvince() + "')";
            }

        }
        int firstIndex = (curPage - 1) * pageSize;
        sql = sql + orderSql + " limit " + pageSize + " offset " + firstIndex;

        List<TaskModelVO> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(TaskModelVO.class));
        Integer total = jdbcTemplate.queryForObject(countSql, Integer.class);
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public DzzhModelSandLiquefaction getModelSandLiquefaction(String taskId,String fina) {
        StringBuilder sql = new StringBuilder("select * from dzzh_model_sand_liquefaction where task_id='").append(taskId).append("'" );
        sql.append(" and del_flag='").append(YNEnum.N.toString()).append("' and result_status='").append(fina).append("'");
        List<DzzhModelSandLiquefaction> query = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzzhModelSandLiquefaction.class));
        return PlatformObjectUtils.isNotEmpty(query)?query.get(0):null;
    }

    @Override
    public DzzhModelSandLiquefaction getModelSandLiquefaction1(String taskId, String fina, String algorithmType) {
        StringBuilder sql = new StringBuilder("select * from dzzh_model_sand_liquefaction where task_id='").append(taskId).append("'" );
        sql.append(" and del_flag='").append(YNEnum.N.toString()).append("' and result_status='").append(fina).append("' and algorithm_type='").append(algorithmType).append("'");
        List<DzzhModelSandLiquefaction> query = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzzhModelSandLiquefaction.class));
        return PlatformObjectUtils.isNotEmpty(query)?query.get(0):null;
    }

    @Override
    public DzzhModelSandLiquefaction getModelSandLiquefaction(String taskId,String dmslId,String fina) {
        StringBuilder sql = new StringBuilder("select * from dzzh_model_sand_liquefaction where del_flag='").append(YNEnum.N.toString()).append("'" );
        if(StringUtils.isNotBlank(taskId)){
            sql.append(" and task_id='").append(taskId).append("'" );
        }
        if(StringUtils.isNotBlank(dmslId)){
            sql.append(" and dmsl_id='").append(dmslId).append("'" );
        }
        if(StringUtils.isNotBlank(fina)){
            sql.append(" and result_status='").append(fina).append("'" );
        }
        List<DzzhModelSandLiquefaction> query = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzzhModelSandLiquefaction.class));
        return PlatformObjectUtils.isNotEmpty(query)?query.get(0):new DzzhModelSandLiquefaction();
    }

    @Override
    public List<DzzhResultSandLiquefaction> getDataByAccelerationAndWaterDepth(String dmslId) {
       String sql = "select earthquake_acceleration,liquefaction_ground_water_depth from dzzh_result_sand_liquefaction " +
               " where dmsl_id='"+dmslId+"' and del_flag='"+YNEnum.N.toString()+"' " +
               "group by earthquake_acceleration,liquefaction_ground_water_depth order by earthquake_acceleration,liquefaction_ground_water_depth";
       return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(DzzhResultSandLiquefaction.class));
    }

    @Override
    public DzzhModelSandLiquefaction getByDmslId(String dmslId) {
        String sql = "select * from dzzh_model_sand_liquefaction where dmsl_id='"+dmslId+"' and del_flag='0'";
        List<DzzhModelSandLiquefaction> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzzhModelSandLiquefaction.class));
        return PlatformObjectUtils.isNotEmpty(query)?query.get(0):null;
    }

    @Override
    public void saveToCenterResult(DzzhModelSandLiquefaction modelSandLiquefaction) {
//        String sql="insert into dzzh_model_sand_liquefaction values(:dmslId , :taskId, :drillVersion, :algorithmType, :operands,:modelType," +
//                " :earthquakeGroup, :adjustFactor, :resultStatus, :evaluateUser, :evaluateUserName,:evaluateTime,:createUser,:createTime,:delFlag,:lithologyVersion," +
//                " :altitudeVersion,:a1Version,:a2Version,:a3Version,:createUserName)";
//        SqlParameterSource source = new BeanPropertySqlParameterSource(modelSandLiquefaction);
//        namedParameterJdbcTemplate.update(sql, source);
        String sql="insert into dzzh_model_sand_liquefaction(dmsl_id,task_id,drill_version,algorithm_type,operands,model_type," +
                "earthquake_group,adjust_factor,result_status,evaluate_user,evaluate_user_name,evaluate_time,create_user,create_time,del_flag,lithology_version,altitude_version,a1_version,a2_version,a3_version,create_user_name,selected_matrix,data_from) " +
                "values(:dmslId , :taskId, :drillVersion, :algorithmType, :operands,:modelType," +
                " :earthquakeGroup, :adjustFactor, :resultStatus, :evaluateUser, :evaluateUserName,:evaluateTime,:createUser,:createTime,:delFlag,:lithologyVersion," +
                " :altitudeVersion,:a1Version,:a2Version,:a3Version,:createUserName,:selectedMatrix,:dataFrom)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(modelSandLiquefaction);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void delResult(String dmslId) {
//        StringBuilder sql = new StringBuilder("update dzzh_model_sand_liquefaction set update_user='"+ PlatformSessionContext.getUserID() +"'");
//        sql.append(",update_time=now(),del_flag='").append(YNEnum.Y.toString()).append("' where dmsl_id='").append(dmslId).append("' and del_flag='").append(YNEnum.N.toString()).append("'");
        StringBuilder sql = new StringBuilder("delete from dzzh_model_sand_liquefaction  where dmsl_id='").append(dmslId).append("' and del_flag='").append(YNEnum.N.toString()).append("'");
        jdbcTemplate.update(sql.toString());
        jdbcTemplate.update(sql.toString());
    }

    @Override
    public void replaceToFinalResult(String dmslId, Integer state, String userId) {
        StringBuilder sql = new StringBuilder("update dzzh_model_sand_liquefaction set update_user='"+ userId +"'");
        sql.append(",update_time=now(),result_status='").append(state).append("' where dmsl_id='").append(dmslId).append("'");
        jdbcTemplate.update(sql.toString());
    }

    @Override
    public void saveResultDetailSandLiquefaction(DzzhResultDetailSandLiquefaction resultDetailSandLiquefaction) {
        StringBuilder sql= new StringBuilder("insert into dzzh_result_sand_liquefaction values(:drslId , :dmslId, :earthquakeAcceleration, :groundWaterDepth, :longitude,:latitude,:liquefactionGroundWaterDepth," +
                " :adjustFactor, :drillCode, :stratumNum, :stratumDepth, :layerThickness,:geotechnicalName,:fullboreTopDepth,:fullboreBottomDepth,:fullboreSoilDepth,:fullboreSoilThickness,:clayContent," +
                ":actualBlowsNumber,:wi,:specificValue,:liquefactionDifferentiate,:drillLiquefactionDifferentiate,:delFlag,:taskId");
        if(PlatformObjectUtils.isNotEmpty(resultDetailSandLiquefaction.getFullboreSoilDepth())){
            sql.append(",:").append("fullboreSoilDepth");
        }
        if(PlatformObjectUtils.isNotEmpty(resultDetailSandLiquefaction.getFullboreSoilThickness())){
            sql.append(",:").append("fullboreSoilThickness");
        }
        if(PlatformObjectUtils.isNotEmpty(resultDetailSandLiquefaction.getWi())){
            sql.append(",:").append("wi");
        }
        sql.append(")");
        SqlParameterSource source = new BeanPropertySqlParameterSource(resultDetailSandLiquefaction);
        namedParameterJdbcTemplate.update(sql.toString(), source);
    }

    @Override
    public void deleteDzzhSandLiquefactionResult(String dmslId){
        jdbcTemplate.update("delete from dzzh_result_sand_liquefaction where  dmsl_id='"+dmslId+"'");
    }

    @Override
    public void delFinalResultByTaskId(String taskId,String userId) {
        StringBuilder sql = new StringBuilder("update dzzh_model_sand_liquefaction set update_user='"+ userId +"'");
        sql.append(",update_time=now(),del_flag='").append(YNEnum.Y.toString()).append("' where task_id='").append(taskId).append("' and del_flag='").
        append(YNEnum.N.toString()).append("' and result_status='").append(ResultEnum.FINA.getState()).append("'");
        jdbcTemplate.update(sql.toString());
    }

    @Override
    public void saveFinalResult(DzzhModelSandLiquefaction modelSandLiquefaction) {
    String sql =
        "insert into dzzh_model_sand_liquefaction(dmsl_id,"
            + "task_id,"
            + "drill_version,"
            + "algorithm_type,"
            + "operands,"
            + "model_type,"
            + "earthquake_group,"
            + "adjust_factor,"
            + "result_status,"
            + "evaluate_user,"
            + "evaluate_user_name,"
            + "evaluate_time,"
            + "create_user,"
            + "create_time,"
            + "del_flag,lithology_version,altitude_version,a1_version,a2_version,a3_version,data_from,create_user_name,can_show,selected_matrix) values(:dmslId , :taskId, :drillVersion, :algorithmType, :operands,:modelType,"
            + " :earthquakeGroup, :adjustFactor, :resultStatus, :evaluateUser, :evaluateUserName,:evaluateTime,:createUser,:createTime,:delFlag,:lithologyVersion," +
                ":altitudeVersion,:a1Version,:a2Version,:a3Version,:dataFrom,:createUserName,:canShow,:selectedMatrix)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(modelSandLiquefaction);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public void batchUpdate(List<String> drslIdList, BigDecimal sumIle) {
        String sql = "update dzzh_result_sand_liquefaction set drill_liquefaction_differentiate='"+sumIle+"' where drsl_id in (:ids)";
        MapSqlParameterSource parameters = new MapSqlParameterSource();
        parameters.addValue("ids", drslIdList);
        namedParameterJdbcTemplate.update(sql, parameters);
    }

    @Override
    public void saveResultDetailSandLiquefaction(List<DzzhResultDetailSandLiquefaction> drslList) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(drslList.toArray());
        StringBuilder sql =
        new StringBuilder(
            "insert into dzzh_result_sand_liquefaction(drsl_id,"
                + "dmsl_id,"
                + "earthquake_acceleration,"
                + "ground_water_depth,"
                + "longitude,"
                + "latitude,"
                + "liquefaction_ground_water_depth,"
                + "adjust_factor,"
                + "drill_code,"
                + "stratum_num,"
                + "stratum_depth,"
                + "layer_thickness,"
                + "geotechnical_name,"
                + "fullbore_top_depth,"
                + "fullbore_bottom_depth,"
                + "clay_content,"
                + "actual_blows_number,"
                + "specific_value,"
                + "liquefaction_differentiate,"
                + "drill_liquefaction_differentiate,"
                + "del_flag,"
                + "task_id,"
                + "fullbore_soil_depth,"
                + "fullbore_soil_thickness,"
                + "wi,"
                + "ei,"
                + "dei,ds,ncr,sand_level,geom,algorithm_type) values(:drslId , :dmslId, :earthquakeAcceleration, :groundWaterDepth, :longitude,:latitude,:liquefactionGroundWaterDepth,"
                + " :adjustFactor, :drillCode, :stratumNum, :stratumDepth, :layerThickness,:geotechnicalName,:fullboreTopDepth,:fullboreBottomDepth,:clayContent,"
                + ":actualBlowsNumber,:specificValue,:liquefactionDifferentiate,:drillLiquefactionDifferentiate,:delFlag,:taskId,:fullboreSoilDepth,:fullboreSoilThickness,:wi,:ei,:dei,:ds,:ncr,:sandLevel,ST_GeometryFromText(:geomText,4490),:algorithmType)");
        namedParameterJdbcTemplate.batchUpdate(sql.toString(), beanSources);
    }


    @Override
    public Boolean getSTIntersects(String geomText, String geomText1) {
        String sql = "SELECT ST_Intersects(st_geometryfromtext(?,4490)," +
                "st_geometryfromtext(?,4490))";
        return jdbcTemplate.queryForObject(sql, Boolean.class, geomText, geomText1);
    }

    @Override
    public void importDrillData(List<String> drills,String taskId, String versionCode) {
        StringBuilder sql = new StringBuilder(
            "insert into dzzh_sand_liquefaction_drill(dsld_id,"
                + "task_id,"
                + "drill_code,"
                + "longitude,"
                + "latitude,"
                + "assise_code,"
                + "bottom_depth,"
                + "layer_thickness,"
                + "geotechnical_name,"
                + "point_num,"
                + "ds,"
                + "reality_num,"
                + "dw,"
                + "pc,"
                + "create_user,"
                + "create_time,"
                + "version_code) values(");
        sql.append("'").append(UUIDGenerator.getUUID()).append("','").append(taskId).append("',");
        drills.forEach(str->{
            sql.append("'").append(str).append("',");
        });

        sql.append("'").append(PlatformSessionContext.getUserID()).append("',now(),'").append(versionCode).append("')");
        jdbcTemplate.update(sql.toString());
    }

    @Override
    public void delete(String taskId, String versionCode) {
        String sql = "delete from dzzh_sand_liquefaction_drill where task_id='"+taskId+"' and version_code='"+versionCode+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public List<SandLiquefactionDrill> getUploadDrillList(String taskId, String versionCode) {
        String sql = "select * from dzzh_sand_liquefaction_drill where task_id='"+taskId+"' and version_code='"+versionCode+"'";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(SandLiquefactionDrill.class));
    }

    @Override
    public void batchSave(List<AhpResultEntity> ahpResultEntityList) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(ahpResultEntityList.toArray());
        StringBuilder sql =
                new StringBuilder(
                        "insert into dzzh_result_ahp(dia_id,"
                                + "task_id,"
                                + "dmsl_id,"
                                + "place_type,"
                                + "pga,"
                                + "user_id,"
                                + "create_time,file_path)"
                                + " values(:diaId , :taskId, :dmslId,  :placeType,:pga,"
                                + " :userId, :createTime,:filePath)");
        namedParameterJdbcTemplate.batchUpdate(sql.toString(), beanSources);
    }

      @Override
    public void saveRatio(String darId, String taskId, String dmslId, String jsonObject) {
        String sql =
            "insert into dzzh_ahp_ratio(dar_id,task_id,dmsl_id,ratio,user_id,create_time) "
                + "values(?,?,?,cast(? as json),?, now())";
        jdbcTemplate.update(sql, darId, taskId, dmslId, jsonObject, PlatformSessionContext.getUserID());
    }

    @Override
    public void deleteAhpRatio(String dmslId){
        jdbcTemplate.update("delete from dzzh_ahp_ratio where dmsl_id='"+dmslId+"'");
    }


    @Override
    public AhpRatio getAhpRatioData(String taskId, String dmslId) {
        String sql =
                "select * from dzzh_ahp_ratio where dmsl_id='"+dmslId+"' and task_id='"+taskId+"'";
        List<AhpRatio> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(AhpRatio.class));
        return PlatformObjectUtils.isNotEmpty(query)?query.get(0):null;
    }


    @Override
    public List<DzzhResultDetailSandLiquefaction> details(String dmslId, BigDecimal earthquakeAcceleration, String liquefactionGroundWaterDepth) {
        StringBuilder sql = new StringBuilder("select * from dzzh_result_sand_liquefaction where dmsl_id='").append(dmslId).append("'");
        sql.append(" and earthquake_acceleration='").append(earthquakeAcceleration).append("'");
        if("实际水位深度".equals(liquefactionGroundWaterDepth)){
            sql.append(" and liquefaction_ground_water_depth>0.00");
        }else{
            sql.append(" and liquefaction_ground_water_depth=").append(liquefactionGroundWaterDepth);
        }
        sql.append(" and del_flag='0' order by longitude,latitude");
        List<DzzhResultDetailSandLiquefaction> query = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzzhResultDetailSandLiquefaction.class));
        return PlatformObjectUtils.isNotEmpty(query)?query:new ArrayList<>();
    }

    @Override
    public List<StandardPenetrationVO> getStandardPenetration(String dmslId, Double earthquakeAcceleration, Double groundWaterDepth) {
        String sql =
                "select * from dzzh_result_sand_liquefaction where dmsl_id='"
                        + dmslId
                        + "' and earthquake_acceleration='"
                        + earthquakeAcceleration
                        + "'  and del_flag='0' ";
        if(groundWaterDepth==1.0){
            sql+=" and ground_water_depth!=0.0 ";
        }else{
            sql+=" and ground_water_depth=0.0 ";
        }
        sql+=" order by drill_code,longitude,latitude";
        List<StandardPenetrationVO> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(StandardPenetrationVO.class));
        return PlatformObjectUtils.isNotEmpty(query)?query:new ArrayList<>();
    }
    @Override
    public List<AhpVO> getAhp(String dmslId, Double earthquakeAcceleration, Double groundWaterDepth) {
        String sql =
                "select * from dzzh_result_sand_liquefaction where dmsl_id='"
                        + dmslId
                        + "' and earthquake_acceleration='"
                        + earthquakeAcceleration
                        + "' and ground_water_depth='"+groundWaterDepth+"' and del_flag='0' order by drill_code,longitude,latitude";
        List<AhpVO> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(AhpVO.class));
        return PlatformObjectUtils.isNotEmpty(query)?query:new ArrayList<>();
    }
    @Override
    public List<AhpResultEntity> getAhpResultList(String dmslId){
        String sql ="select * from dzzh_result_ahp where dmsl_id='"+ dmslId+ "'";
        List<AhpResultEntity> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(AhpResultEntity.class));
        if(PlatformObjectUtils.isNotEmpty(query)){
            return query;
        }
        return new ArrayList<>();
    }

    @Override
    public List<AhpResultEntity> getAhpResultListByTaskId(String dmslId,String taskId){
        String sql ="select * from dzzh_result_ahp where dmsl_id='"+ dmslId+ "' and task_id='"+taskId+"'";
        List<AhpResultEntity> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(AhpResultEntity.class));
        if(PlatformObjectUtils.isNotEmpty(query)){
            return query;
        }
        return new ArrayList<>();
    }

    @Override
    public Integer hasAhpResult(String dmslId){
        String sql ="select count(1) from dzzh_result_ahp where dmsl_id='"+ dmslId+ "'";
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

    @Override
    public void deleteAhpResult(String dmslId){
        jdbcTemplate.update("delete from dzzh_result_ahp where dmsl_id='"+ dmslId+ "'");
    }
    @Override
    public List<DzzhResultDetailSandLiquefaction> getResultDetailSandLiquefactionList(String dmslId){
        String sql =
                "select *,st_astext(geom) as geomText from dzzh_result_sand_liquefaction where dmsl_id='"+ dmslId + "' and  del_flag='0'";
        List<DzzhResultDetailSandLiquefaction> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DzzhResultDetailSandLiquefaction.class));
        return PlatformObjectUtils.isNotEmpty(query)?query:new ArrayList<>();
    }

    @Override
    public Map<String, Object> getCenterDataPage(String taskId, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select * from dzzh_model_sand_liquefaction");
        StringBuilder whereSql = new StringBuilder(" where task_id='"+taskId+"' and result_status='"+ResultEnum.CENTER.getState()+"' and del_flag='0'");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        StringBuilder countSql = new StringBuilder(" select count(*) from dzzh_model_sand_liquefaction");
        int firstIndex = (curPage - 1) * pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + pageSize + " offset " + firstIndex);
        String sqlWhole = sql.append(whereSql).append(orderSql).append(limitSql).toString();
        countSql.append(whereSql);
        List<DzzhModelSandLiquefaction> list = jdbcTemplate.query(sqlWhole, new BeanPropertyRowMapper<>(DzzhModelSandLiquefaction.class));
        Integer total = jdbcTemplate.queryForObject(countSql.toString(), Integer.class);
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public BigDecimal getNearestDistanceByDrill(BigDecimal longitude, BigDecimal latitude, BigDecimal longitude1, BigDecimal latitude1) {
        //String sql = "select st_distance(st_geometryfromtext('POINT(' || ? || ' ' || ? || ')',4490),st_geometryfromtext('POINT(' || ? || ' ' || ? || ')',4490))";
        String sql = "select ST_Length(ST_Transform(st_geomfromtext('LINESTRING(' || ? || ' ' || ? || ',' || ? || ' ' || ? || ')',4490),4527))";
        return jdbcTemplate.queryForObject(sql, BigDecimal.class, longitude,latitude,longitude1,latitude1);
    }

    @Override
    public BigDecimal getNearestBoundaryDistance(BigDecimal longitude, BigDecimal latitude,String taskId) {
        //根据空间数据wkt获取投影坐标EPSG
        int epsg = elevationMapService.getEPSGFromGeomText("POINT("+longitude+" "+latitude+")");
        String sql = "select ST_Length(ST_Transform(("
            + " select ST_ShortestLine(a.geom,st_geometryfromtext('POINT(' || ? || ' ' || ? || ')',4490)) from ("
            + " SELECT ST_MakeLine(sp,ep) as geom  FROM "
            + " (SELECT ST_PointN(geom, generate_series(1, ST_NPoints(geom)-1)) as sp,"
            + " ST_PointN(geom, generate_series(2, ST_NPoints(geom))) as ep  FROM (SELECT (ST_Dump(ST_Boundary(t.geom))).geom "
            + " FROM (select * from dzzh_task where dt_id=?) t) AS linestrings) AS segments) a "
            + " ORDER BY st_distance(a.geom,st_geometryfromtext('POINT(' || ? || ' ' || ? || ')',4490)) LIMIT 1 ),"+epsg+"))";
        return jdbcTemplate.queryForObject(sql, BigDecimal.class, longitude,latitude,taskId,longitude,latitude);
    }

    @Override
    public void saveSandCircle(List<SandCircleVO> sandCircleVOS) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(sandCircleVOS.toArray());
        StringBuilder sql = new StringBuilder("insert into dzzh_result_sand_circle(drsc_id,"
                                + "task_id,dmsl_id,geom,earthquake_acceleration,liquefaction_ground_water_depth,sand)"
                                + " values(:drscId , :taskId, :dmslId, ST_GeomFromText(:geom,4490), :earthquakeAcceleration,:liquefactionGroundWaterDepth,:sand)");
        namedParameterJdbcTemplate.batchUpdate(sql.toString(), beanSources);
    }

    @Override
    public void updateFinalModel(String dmslId, String taskId, Integer state, String flag) {
        StringBuilder sql = new StringBuilder("update dzzh_model_sand_liquefaction set ");
        if("1".equals(flag)){
            sql.append(" result_status='").append(ResultEnum.CENTER.getState()).append("',");
        }
        if("0".equals(flag)){
            sql.append(" del_flag='").append(YNEnum.Y.toString()).append("',");
        }
        sql.append(" update_user='").append(PlatformSessionContext.getUserID()).append("',");
        sql.append(" update_time=now()");
        jdbcTemplate.update(sql.toString());
    }

    @Override
    public void saveAhpMathWeight(AhpMathWeight ahpMathWeight) {
    String sql =
        "insert into dzzh_ahp_math_weight(damw_id,"
            + "task_id,"
            + "dmsl_id,"
            + "solum_category_weight1,"
            + "solum_category_weight2,"
            + "solum_category_weight3,"
            + "solum_category_weight4,"
            + "landform_category_weight1,"
            + "landform_category_weight2,"
            + "landform_category_weight3,"
            + "landform_category_weight4,"
            + "landform_category_weight5,"
            + "solum_factor_weight,"
            + "landform_factor_weight,"
            + "pga_category_weight1,"
            + "pga_category_weight2,"
            + "pga_factor_weight,"
            + "river_category_weight1,"
            + "river_category_weight2,"
            + "river_category_weight3,"
            + "river_category_weight4,"
            + "river_category_weight5,"
            + "river_factor_weight,"
            + "place_category_weight1,"
            + "place_category_weight2,"
            + "place_category_weight3,"
            + "place_factor_weight) values(:damwId , :taskId, :dmslId, :solumCategoryWeight1, :solumCategoryWeight2,:solumCategoryWeight3,"
            + " :solumCategoryWeight4, :solumFactorWeight, :landformCategoryWeight1, :landformCategoryWeight2, :landformCategoryWeight3,:landformCategoryWeight4,:landformCategoryWeight5,:landformFactorWeight,:pgaCategoryWeight1,:pgaCategoryWeight2,"
            + ":pgaFactorWeight,:riverCategoryWeight1,:riverCategoryWeight2,:riverCategoryWeight3,:riverCategoryWeight4,:riverCategoryWeight5,:riverFactorWeight,:placeCategoryWeight1,:placeCategoryWeight2,:placeCategoryWeight3,:placeFactorWeight)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(ahpMathWeight);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public AhpMathWeight getAhpMathWeight(String dmslId, String dtId) {
        String sql ="select * from dzzh_ahp_math_weight where task_id='"+dtId+"' and dmsl_id='"+dmslId+"'";
        List<AhpMathWeight> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(AhpMathWeight.class));
        return PlatformObjectUtils.isNotEmpty(query) && query.size()>0?query.get(0):new AhpMathWeight();
    }

    @Override
    public void batchSavePolygonEvaluateResult(List<SandPolygonEvaluateResultVO> voList) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(voList.toArray());
    String sql =
        "insert into dzzh_sand_liquefaction_polygon_evaluate_result(dslper_id,"
            + "dmsl_id,"
            + "value,"
            + "geom,"
            + "earthquake_acceleration,"
            + "ground_water_depth,algorithm_type,task_id) "
            + " values("
            + ":dslperId, "
            + ":dmslId, "
            + ":value, "
            + "ST_GeometryFromText(:geom,4490), "
            + ":earthquakeAcceleration,:groundWaterDepth,:algorithmType,:taskId )";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    @Override
    public List<DzzhResultSandLiquefaction> getDataById(String dmslId) {
        String sql = "select earthquake_acceleration,liquefaction_ground_water_depth from dzzh_result_sand_liquefaction " +
                " where dmsl_id='"+dmslId+"' and del_flag='"+YNEnum.N.toString()+"' " +
                "group by earthquake_acceleration,liquefaction_ground_water_depth HAVING liquefaction_ground_water_depth>0.00 order by earthquake_acceleration";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(DzzhResultSandLiquefaction.class));
    }

    @Override
    public List<DzzhResultDetailSandLiquefaction> getResultDataById(String dmslId) {
        String sql = "select * from dzzh_result_sand_liquefaction " +
                " where dmsl_id='"+dmslId+"' and del_flag='"+YNEnum.N.toString()+"'";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(DzzhResultDetailSandLiquefaction.class));
    }

    @Override
    public void deleteByTaskId(String dtId) {
        //String sql = "delete from dzzh_count_buffer where task_id = '"+dtId+"' and type1='2' and flag='2'";
        String sql = "delete from dzzh_count_buffer where   type1='2'";
        if(StringUtils.isNotBlank(dtId)){
            sql+=" and task_id = '"+dtId+"' ";
        }
        jdbcTemplate.update(sql);
    }

    @Override
    public Integer getRiverNum(String dtId) {
        String sql = "select count(*) from dzzh_count_buffer where task_id = '"+dtId+"' and type1='2'";
        try{
            return jdbcTemplate.queryForObject(sql, Integer.class);
        }catch (Exception ex){
            return 0;
        }

    }

    @Override
    public void saveDtis(String uuid, String taskId, String zhdxzs, String cdlb1, String cdlb2, String gcFileName, String dmslId) {
        String sql = "insert into dzzh_dtis(ddtis_id,task_id,dmsl_id,gc_file_name,zhdxzs,cdlb1,cdlb2) values('"+uuid+"','"+taskId+"','"+dmslId+"','"+gcFileName+"','"+zhdxzs+"','"+cdlb1+"','"+cdlb2+"')";
        jdbcTemplate.update(sql);
    }
    @Override
    public void delDtis(String dmslId){
        jdbcTemplate.update("delete from dzzh_dtis where dmsl_id='"+dmslId+"'");
    }

    @Override
    public OmprehensiveTerrainIndexAndSite getOmprehensiveTerrainIndexAndSiteByDmslId(String dmslId) {
        String sql = "select * from dzzh_dtis " +
                " where dmsl_id='"+dmslId+"' ";
        List<OmprehensiveTerrainIndexAndSite> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(OmprehensiveTerrainIndexAndSite.class));
        return PlatformObjectUtils.isNotEmpty(query)&&query.size()>0?query.get(0):null;
    }

    @Override
    public OmprehensiveTerrainIndexAndSite getOmprehensiveTerrainIndexAndSite(String taskId, String dmslId) {
        String sql = "select * from dzzh_dtis  where task_id='"+taskId+"'";
        if(StringUtils.isNotBlank(dmslId)){
            sql+=" and dmsl_id='"+dmslId+"'";
        }
        List<OmprehensiveTerrainIndexAndSite> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(OmprehensiveTerrainIndexAndSite.class));
        return PlatformObjectUtils.isNotEmpty(query)&&query.size()>0?query.get(0):null;
    }

    @Override
    public List<SandLiquefactionDrill> getAllDrillForDp() {
        String sql = "select * from dzzh_sand_liquefaction_drill ";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(SandLiquefactionDrill.class));
    }

    @Override
    public void save(String uuid, String taskId, String fileName1,String fileName2,String gcFileName,String dmslId) {
        String sql = "insert into dzzh_dtis(ddtis_id,task_id,gc_file_name,dmsl_id";
        if(StringUtils.isBlank(fileName2)){
            sql+=",zhdxzs) values('"+uuid+"','"+taskId+"','"+gcFileName+"','"+dmslId+"','"+fileName1+"')";
        }else{
            sql+=",cdlb1,cdlb2) values('"+uuid+"','"+taskId+"','"+gcFileName+"','"+dmslId+"','"+fileName1+"','"+fileName2+"')";
        }
        jdbcTemplate.update(sql);
    }

    @Override
    public void update(String ddtisId, String fileName1, String fileName2,String gcFileName) {
        String sql = "update dzzh_dtis set gc_file_name='"+gcFileName+"'";
        if(StringUtils.isBlank(fileName2)){
            sql+=", zhdxzs='"+fileName1+"' ";
        }else{
            sql+=", cdlb1='"+fileName1+"',cdlb2='"+fileName2+"' ";
        }
        sql+=" where ddtis_id='"+ddtisId+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public DzzhDtis getMapResultFile(String taskId,String dmslId) {
        StringBuilder sql = new StringBuilder("select  * from dzzh_dtis where task_id='").append(taskId).append("' and dmsl_id='"+dmslId+"'");
        List<DzzhDtis> query = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(DzzhDtis.class));
        return PlatformObjectUtils.isNotEmpty(query)?query.get(0):new DzzhDtis();
    }

    @Override
    public void updateGcVersion(String dmslId, String teraVersion) {
        String sql = "update dzzh_model_sand_liquefaction set altitude_version='"+teraVersion+"',update_user='"+PlatformSessionContext.getUserID()+"',update_time=now() where dmsl_id='"+dmslId+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public String getFinalMapResultFile(String taskId, String dmslId, String landform, String pga) {
        StringBuilder sql = new StringBuilder("select  file_path from dzzh_result_ahp where task_id='").append(taskId).append("' and dmsl_id='").append(dmslId).append("' and place_type='").append(landform).append("' and pga='").append(pga).append("'");
        return jdbcTemplate.queryForObject(sql.toString(),String.class);
    }
}
